from flask import Flask, jsonify, request
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
from flask_cors import CORS

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///travel.db'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
CORS(app, resources={r"/*": {"origins": "http://localhost:5173"}})

class Route(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    description = db.Column(db.Text)
    activities = db.Column(db.JSON, nullable=False)  # 存储活动数组
    transports = db.Column(db.JSON, nullable=False)  # 存储通勤数组
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    def to_dict(self):
        return {
            'id': self.id,
            'name': self.name,
            'description': self.description,
            'activities': self.activities,
            'transports': self.transports,
            'created_at': self.created_at.isoformat(),
            'updated_at': self.updated_at.isoformat()
        }

@app.route('/')
def index():
    return jsonify({"message": "TravelVision Backend"})

@app.route('/routes', methods=['GET'])
def get_routes():
    routes = Route.query.all()
    return jsonify([route.to_dict() for route in routes])

@app.route('/routes', methods=['POST'])
def create_route():
    data = request.get_json()
    new_route = Route(
        name=data['name'],
        description=data.get('description', ''),
        activities=data['activities'],
        transports=data['transports']
    )
    db.session.add(new_route)
    db.session.commit()
    return jsonify(new_route.to_dict()), 201

@app.route('/routes/<int:route_id>', methods=['GET'])
def get_route(route_id):
    route = Route.query.get_or_404(route_id)
    return jsonify(route.to_dict())

@app.route('/upload', methods=['POST'])
def upload_file():
    if 'file' not in request.files:
        return jsonify({'error': 'No file uploaded'}), 400
    
    file = request.files['file']
    if file.filename == '':
        return jsonify({'error': 'No selected file'}), 400
    
    try:
        if file.filename.endswith('.csv'):
            import csv
            activities = []
            transports = []
            reader = csv.DictReader(file.stream.read().decode('utf-8').splitlines())
            for row in reader:
                lng, lat = map(float, row['经纬度(高德)'].split(','))
                activities.append({
                    'trip_id': row.get('trip_id', ''),
                    'date': row.get('date', ''),
                    'day_number': int(row.get('day_number', 1)),
                    'start_time': row.get('start_time', ''),
                    'end_time': row.get('end_time', ''),
                    'time_slot': row.get('time_slot', ''),
                    'order_index': int(row.get('order_index', 1)),
                    'type': row.get('type', ''),
                    'title': row.get('title', ''),
                    'location': {
                        'name': row.get('地点名称', ''),
                        'address': row.get('地址', ''),
                        'coordinates': {'longitude': lng, 'latitude': lat}
                    },
                    'details': {
                        'cost': int(row.get('费用(元)', 0)),
                        'reservation_required': row.get('reservation_required', 'false') == 'true',
                        'suggested_duration': int(row.get('suggested_duration', 60))
                    }
                })
            return jsonify({'activities': activities, 'transports': transports}), 200
        
        elif file.filename.endswith(('.xlsx', '.xls')):
            import pandas as pd
            activities = []
            transports = []
            df = pd.read_excel(file)
            for _, row in df.iterrows():
                lng, lat = map(float, row['经纬度(高德)'].split(','))
                activities.append({
                    'trip_id': row.get('trip_id', ''),
                    'date': row.get('date', ''),
                    'day_number': int(row.get('day_number', 1)),
                    'start_time': row.get('start_time', ''),
                    'end_time': row.get('end_time', ''),
                    'time_slot': row.get('time_slot', ''),
                    'order_index': int(row.get('order_index', 1)),
                    'type': row.get('type', ''),
                    'title': row.get('title', ''),
                    'location': {
                        'name': row.get('地点名称', ''),
                        'address': row.get('地址', ''),
                        'coordinates': {'longitude': lng, 'latitude': lat}
                    },
                    'details': {
                        'cost': int(row.get('费用(元)', 0)),
                        'reservation_required': row.get('reservation_required', 'false') == 'true',
                        'suggested_duration': int(row.get('suggested_duration', 60))
                    }
                })
            return jsonify({'activities': activities, 'transports': transports}), 200
        
        elif file.filename.endswith('.json'):
            import json
            try:
                data = json.load(file.stream)
                # print(f"DEBUG: Loaded JSON data: {data}")  # 记录原始数据
                
                if not isinstance(data, dict) or 'itinerary' not in data:
                    print(f"ERROR: Invalid JSON structure: {type(data)}")
                    return jsonify({'error': 'Invalid JSON structure: expected itinerary array'}), 400
                
                activities = []
                transports = []
                
                for day in data['itinerary']:
                    print(f"DEBUG: Processing day {day.get('day_number')}")
                    for item in day.get('items', []):
                        print(f"DEBUG: Processing item {item.get('trip_id')}")
                        if item.get('type') == 'transport':
                            transports.append(item)
                        else:
                            activities.append(item)
                
                print(f"DEBUG: Parsed {len(activities)} activities and {len(transports)} transports")
                return jsonify({'activities': activities, 'transports': transports}), 200
                
            except json.JSONDecodeError as e:
                print(f"ERROR: JSON decode error: {str(e)}")
                return jsonify({'error': f'Invalid JSON format: {str(e)}'}), 400
            except Exception as e:
                print(f"ERROR: Unexpected error: {str(e)}")
                return jsonify({'error': f'Unexpected error: {str(e)}'}), 500
        
        else:
            return jsonify({'error': 'Unsupported file format'}), 400
    except Exception as e:
        return jsonify({'error': str(e)}), 500

with app.app_context():
    db.create_all()

def add_sample_routes():
    with app.app_context():
        if not Route.query.first():
            sample_routes = [
                Route(
                    name='北京经典一日游',
                    description='天安门-故宫-景山公园经典路线',
                    points=[
                        {'lng': 116.3950, 'lat': 39.8995, 'name': '方砖厂69号炸酱面(前门店)', 'address': '北京市西城区前门大街', 'time': '12:30-14:00', 'type': '餐饮', 'fee': 35, 'reservation': '无需'},
                        {'lng': 116.3955, 'lat': 39.8998, 'name': '前门大街', 'address': '北京市西城区前门大街', 'time': '14:00-17:00', 'type': '景点', 'fee': 0, 'reservation': '无需'},
                        {'lng': 116.3982, 'lat': 39.9005, 'name': '门框胡同百年卤煮', 'address': '北京市西城区门框胡同', 'time': '17:00-18:30', 'type': '餐饮', 'fee': 40, 'reservation': '无需'},
                        {'lng': 116.3940, 'lat': 39.9015, 'name': '正阳门箭楼', 'address': '北京市东城区前门大街北端', 'time': '19:00-20:00', 'type': '景点', 'fee': 0, 'reservation': '无需'},
                        {'lng': 116.3920, 'lat': 39.9012, 'name': '护国寺小吃(前门店)', 'address': '北京市西城区前门西街', 'time': '07:00-08:00', 'type': '餐饮', 'fee': 15, 'reservation': '无需'},
                        {'lng': 116.3975, 'lat': 39.9032, 'name': '天安门广场', 'address': '北京市东城区东长安街', 'time': '08:00-09:00', 'type': '景点', 'fee': 0, 'reservation': '需预约'}
                    ]
                )
            ]
            db.session.add_all(sample_routes)
            db.session.commit()

if __name__ == '__main__':
    with app.app_context():
        db.drop_all()
        db.create_all()
    # add_sample_routes()
    app.run(debug=True)